package com.ruc.deke.service;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Set;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.ruc.deke.dao.SamplesDAO;
import com.ruc.deke.model.DBRecord;
import com.ruc.deke.utils.ParseUtil;

/**
 * 具体的判断逻辑实现
 * 
 * @author: cyj
 * @date: 2017年3月31日 下午2:57:19
 */

@Service(value = "sqlJudgeServiceImpl")
public class SqlJudgeServiceImpl implements SqlJudgeService {

	@Resource(name = "samplesDAOImpl")
	private SamplesDAO samplesDAO;

	@Resource(name = "dataSource_rucsamples")
	private DataSource dataSource;

	public void buildEnvir(Connection conn, Connection dbConn, int beforeModuleId, int beforeExpId) {
		if (beforeExpId == 0 || beforeModuleId == 0)
			return;
		Statement stmt = null;
		Statement stmt2 = null;
		ResultSet rs = null;
		try {
			stmt2 = conn.createStatement();
			stmt = dbConn.createStatement();
			String sqlQureybeforeSolution = "select  * from EXP_MODULE where EXPID=" + beforeExpId + " and MODULEID= "
					+ beforeModuleId;
			System.out.println(sqlQureybeforeSolution);
			rs = stmt.executeQuery(sqlQureybeforeSolution);
			ArrayList<DBRecord> list = ParseUtil.toDBRecord(rs);
			System.out.println(list.get(0).getString("SOLUTION"));
			stmt2.execute(list.get(0).getString("SOLUTION"));
			buildEnvir(conn, dbConn, list.get(0).getInt("BEFOREMODULEID"), list.get(0).getInt("BEFOREEXPID"));

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	@Override
	public int ExplainJudge(Connection conn, Connection dbConn, String userSolution, String teacherSoultion,
			int befModuleId, int befExpId) {
		System.out.println(befModuleId + "-----------" + befExpId);
		Statement stmt = null;
		Savepoint sp = null;
		Integer score = 0;
		boolean isEqual = false;
		List<String> teacherExplain = new ArrayList<>();
		List<String> studentExplain = new ArrayList<>();
		ResultSet teacherRs = null, studentRs = null;
		System.out.println("======== rollback start=======" + userSolution);
		// 该题目的extra信息 判分规则

		System.out.println(teacherSoultion + "!!!!!!!!!!");
		try {
			stmt = conn.createStatement();
			conn.setAutoCommit(false);
			System.out.println(" execute----teacher ---answer");
			// 设置保存点，下面执行的在检查完成后都将回滚到此处的状态从而不改变数据库中的数据
			sp = conn.setSavepoint();
			// System.out.println(sp);
			buildEnvir(conn, dbConn, befModuleId, befExpId);
			// 根据具体不同的那一道题目进行不同的处理
			boolean hasResultSet = stmt.execute("explain " + teacherSoultion);
			if (hasResultSet) { // 如果执行后有ResultSet结果集
				teacherRs = stmt.getResultSet();
				// 获取结果集
				ResultSetMetaData rsmd = teacherRs.getMetaData();
				// ResultSetMetaData是用于分析结果集的元数据接口
				int columnCount = rsmd.getColumnCount();

				while (teacherRs.next()) {// 输出ResultSet对象
					for (int i = 0; i < columnCount; i++) {
						teacherExplain.add(teacherRs.getString(i + 1));
						System.out.print(teacherRs.getString(i + 1) + "/t");
					}
					System.out.print("/n");
				}
			} else {
				System.out.println("该SQL语句影响的记录有" + stmt.getUpdateCount() + "条");
			}

			conn.rollback(sp);

		} catch (java.sql.SQLException e) {
			System.out.println("===rollback exception");
			score = 0;
			System.out.println("exception:" + e.getMessage());

		} finally {
			try {
				stmt.close();
				System.out.println("====conn.close end====");
			} catch (SQLException e) {
				System.out.println("====close exception====");
				System.out.println("exception:" + e.getMessage());
			}
		}

		try {
			stmt = conn.createStatement();
			System.out.println(" execute----student ---answer");
			conn.setAutoCommit(false);
			sp = conn.setSavepoint();
			// System.out.println(sp);
			buildEnvir(conn, dbConn, befModuleId, befExpId);
			Boolean hasResultSet = stmt.execute("explain " + userSolution);
			if (hasResultSet) { // 如果执行后有ResultSet结果集
				studentRs = stmt.getResultSet();
				// 获取结果集
				// System.out.println("{{{{" + studentRs.toString() + "}}}}");
				ResultSetMetaData rsmd = studentRs.getMetaData();
				// ResultSetMetaData是用于分析结果集的元数据接口
				int columnCount = rsmd.getColumnCount();

				while (studentRs.next()) {// 输出ResultSet对象
					for (int i = 0; i < columnCount; i++) {
						studentExplain.add(studentRs.getString(i + 1));
						System.out.print(studentRs.getString(i + 1) + "/t");
					}
					System.out.print("/n");
				}
			} else {
				System.out.println("该SQL语句影响的记录有" + stmt.getUpdateCount() + "条");
			}

			conn.rollback(sp);
		} catch (java.sql.SQLException e) {
			System.out.println("===rollback exception");
			score = 0;
			System.out.println("exception:" + e.getMessage());
			return score;
		} finally {
			try {
				stmt.close();
				System.out.println("====conn.close end====");
			} catch (SQLException e) {
				System.out.println("====close exception====");
				System.out.println("exception:" + e.getMessage());
			}
		}
		System.out.println(teacherExplain.toString() + "====比较====" + studentExplain.toString());
		if (teacherExplain.toString().equals(studentExplain.toString())) {
			isEqual = true;
		}

		if (isEqual) {
			score = 10;
		} else {
			score = 0;
		}

		System.out.println("========rollback judge end===" + score + isEqual + "====");
		return score;
	}
	@Override
	public int unExplainJudge(Connection conn, Connection dbConn, String userSolution, String teacherSoultion,
			int befModuleId, int befExpId) {
		Statement stmt = null;
		Savepoint sp = null;
		Integer score = 0;
		Similarity similarity = new Similarity();
		float similarityRatio = 0;
		// 设置保存点，下面执行的在检查完成后都将回滚到此处的状态从而不改变数据库中的数据
		try {
			stmt = conn.createStatement();
			conn.setAutoCommit(false);
			sp = conn.setSavepoint();
			buildEnvir(conn, dbConn, befModuleId, befExpId);
			stmt.execute(userSolution);
			conn.rollback(sp);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			// 抛出异常语法错误。零分
			e.printStackTrace();
			score = 0;
			return score;
		}
		// System.out.println(sp);
		similarityRatio = similarity.getSimilarityRatio(Similarity.replaceBlank(userSolution),
				Similarity.replaceBlank(teacherSoultion));
		System.out.println("similarityRatio=" + similarityRatio);
		if (similarityRatio > 0.9) {
			score = 10;
		} else if (similarityRatio >= 0.5 && similarityRatio < 0.9) {
			score = (int) (similarityRatio * 10);
		} else {
			score = 0;
		}

		return score;
	}
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	//————————————————————————————————————————————————————————————————————————————————————
	@Override
	public int selectJudge(Connection conn, String standardSolution, String userSolution, String extraString) {
		int score = 0;
		// 该题目的extra信息
		System.out.println(userSolution);
		JSONObject jsonObj = JSON.parseObject(extraString);
		System.out.println("jsonObj-----"+jsonObj);
		String rule = jsonObj.getString("rule");// 是否有辅助判断的sql语句：“false”，“true”
		String extraType = jsonObj.getString("type");// extraType:select_normal,select_order两种

		System.out.println("userSolution-----"+userSolution);
		boolean answerCompare = isResultSetEqual(conn, standardSolution, userSolution, extraType);
		// System.err.println(answerCompare+"");
		if (answerCompare) {
			score = 10;
		} else {
			if (rule.equals("true")) {// 是否有辅助判断
				JSONObject jObj = JSON.parseObject(extraString);
				String extraSql = jObj.getString("sql");
				JSONArray jsonArray = JSON.parseArray(extraSql);
				//System.err.println("#&");
				for (int i = 0; i < jsonArray.size(); i++) {
					JSONObject Obj = jsonArray.getJSONObject(i);
					String halfAnsSql = Obj.getString("sql");
					if(halfAnsSql=="" || halfAnsSql==null)continue;
					int value = Obj.getInteger("value");
					//System.out.println("###############"+value+"###############");
					if (isResultSetEqual(conn, halfAnsSql, userSolution, extraType)) {
						score = value;
						break;
					} else {
						score = 0;
						System.err.println("##");
					}
				}
			} else {
				score = 0;
			}
		}
		System.out.println("had judged : " + userSolution);
		return score;
	}

	@Override
	public int updateJudge(Connection conn, String userSolution, String extraString) {
		int iCode = 0;
		// Connection conn=null;
		Statement stmt = null;
		Savepoint sp = null;
		Integer score = 0;
		System.out.println("========update judge start=======" + userSolution);
		// 该题目的extra信息 判分规则
		JSONObject jsonObj = JSON.parseObject(extraString);
		String extraSql = jsonObj.getString("sql");
		Integer extraValue = jsonObj.getInteger("value");
		String extraType = jsonObj.getString("type");

		try {
			// conn=dataSource.getConnection();
			conn.setAutoCommit(false);// 通知数据库开启事务(start transaction)
			stmt = conn.createStatement();
			String value = samplesDAO.queryOne(conn, extraSql);
			System.out.println("before execute----" + value);
			// 设置保存点，下面执行的在检查完成后都将回滚到此处的状态从而不改变数据库中的数据
			sp = conn.setSavepoint();
			System.out.println(sp);
			// 根据具体不同的那一道题目进行不同的处理
			
			if (extraType.equals("insert1")) { 
				System.out.println("========insert1 judge =======");
				iCode = stmt.executeUpdate(userSolution);
				value = samplesDAO.queryOne(conn, extraSql);
				score = Integer.parseInt(value) == extraValue ? 10 : 0;
			} else if (extraType.equals("insert2")) {
				System.out.println("========insert2 judge =======");
				String preSql = "insert into student values('2017001001','李晓明','男',21,'信息学院')";
				stmt.executeUpdate(preSql);
				iCode = stmt.executeUpdate(userSolution);
				ArrayList<DBRecord> record = null;
				try {
					record = samplesDAO.query(conn, extraSql);
				} catch (Exception e) {
					System.out.println("==insert2 exception===");
					// e.printStackTrace();
					System.out.println("exception:" + e.getMessage());
				}
				score = record.size() == 0 ? 10 : 0;

			} else if (extraType.equals("update1")) {
				System.out.println("========update1 judge =======" + userSolution);
				ArrayList<DBRecord> record1 = null;
				ArrayList<DBRecord> record2 = null;
				try {
					record1 = samplesDAO.query(conn, extraSql);
					iCode = stmt.executeUpdate(userSolution);
					System.err.println(iCode + "-----------");
					record2 = samplesDAO.query(conn, extraSql);
					
					System.out.println(userSolution);
					System.out.println(extraSql);
				} catch (Exception e) {
					score = 0;
					// e.printStackTrace();
					System.out.println("update1exception:" + e.getMessage());
				}

				System.out.println("===to if===");
				if (record1 != null && record2 != null && record1.size() > 0 && record2.size() > 0) {
					boolean isAll = true;
					for (int i = 0; i < record1.size(); i = i + 3) {
						System.out.println("=====for xunuan===");
						int result = record2.get(i).getInt("GRADE") - record1.get(i).getInt("GRADE");
						if (result == 10)
							continue;
						else
							isAll = false;
					}
					score = isAll ? 10 : 0;
					System.out.println("===out for===");
				}

			} else if (extraType.equals("update2")) {
				System.out.println("========update2 judge =======" + userSolution);
				// 先进性前序操作 将李晓明选上和赵古韵相同的课程
				String preSql1 = "insert into student values('2017001001','李晓明','男',21,'信息学院')";
				stmt.executeUpdate(preSql1);
				String preSql2 = "insert into SC(sno,cno,grade) select '2017001001' as sno,cno,grade from SC where sno in (select sno from student where sname='赵古韵')";
				stmt.executeUpdate(preSql2);
				String value1 = samplesDAO.queryOne(conn, extraSql);
				stmt.executeUpdate(userSolution);
				String value2 = samplesDAO.queryOne(conn, extraSql);
				score = (Integer.parseInt(value1) > 0 && Integer.parseInt(value2) == 0) ? 10 : 0;
			}
			conn.rollback(sp);
			System.out.println("====rollback===");
		} catch (java.sql.SQLException e) {
			System.out.println("===rollback exception");
			score = 0;
			System.out.println("exception:" + e.getMessage());

		} finally {
			try {
				stmt.close();
				System.out.println("====conn.close end====");
			} catch (SQLException e) {
				System.out.println("====close exception====");
				System.out.println("exception:" + e.getMessage());
			}
		}
		System.out.println("========update judge end=======");
		return score;
	}
	
	//11.8周坤-----update
	public int updateJudge1(Connection conn, String userSolution,String solution, String extraString) {
		
		int iCode = 0;
		// Connection conn=null;
		Statement stmt = null;
		Savepoint sp = null;
		Integer score = 0;
		System.out.println("========update judge start=======" + userSolution);
		// 该题目的extra信息 判分规则
		JSONObject jsonObj = JSON.parseObject(extraString);
		//String extraSql = jsonObj.getString("sql");
		Integer extraValue = jsonObj.getInteger("value");
		String rule = jsonObj.getString("rule");// 是否有辅助判断的sql语句：“false”，“true”
		String extraType = jsonObj.getString("type");
		try {
			conn.setAutoCommit(false);// 通知数据库开启事务(start transaction)
			stmt = conn.createStatement();
			// 设置保存点，下面执行的在检查完成后都将回滚到此处的状态从而不改变数据库中的数据
			sp = conn.setSavepoint();
			
			if (extraType.equals("insert")) { //SQL语句为insert
			String str1=insertToSelect(solution);
			System.out.println("______________________________");
			System.out.println(str1);
			System.out.println("______________________________");
			ArrayList<DBRecord> record1 = null;
			ArrayList<DBRecord> record2 = null;
			iCode =stmt.executeUpdate(solution);
			System.err.println(iCode + "-----------");
			record1 = samplesDAO.query(conn, str1);
			conn.rollback(sp);
			
			Savepoint sp1 = null;
			sp1 = conn.setSavepoint();
			iCode=stmt.executeUpdate(userSolution);
			System.err.println(iCode + "-----------");
			record2 = samplesDAO.query(conn, str1);
			score=0;
			if(record2.equals(record1)) {score=10;}
			conn.rollback(sp1);
			if(score==0) {
				if (rule.equals("true")) {// 是否有辅助判断
					JSONObject jObj = JSON.parseObject(extraString);
					String extraSql = jObj.getString("sql");
					JSONArray jsonArray = JSON.parseArray(extraSql);
					for (int i = 0; i < jsonArray.size(); i++) {
						JSONObject Obj = jsonArray.getJSONObject(i);
						String halfAnsSql = Obj.getString("sql");
						int value = Obj.getInteger("value");
						if (extraJudge(conn, halfAnsSql, userSolution, extraType)) {
							score = value;
							break;
						} else {
							score = 0;
						}
					}
				} else {
					score = 0;
				}
				
			}
			}
			else if(extraType.equals("update")) {
				String str1=updateToSelect(solution);
				String str2=updateToSelect(userSolution);
				ArrayList<DBRecord> record1 = null;
				ArrayList<DBRecord> record2 = null;
				System.out.println("__________________________");
				System.out.println(str1);
				System.out.println(str2);
				System.out.println("__________________________");
				record1 = samplesDAO.query(conn, str1);
				//System.out.println(solution);
				iCode =stmt.executeUpdate(solution);
				System.err.println(iCode + "-----------");
				record2 = samplesDAO.query(conn, str1);
				conn.rollback(sp);
				
				Savepoint sp1 = null;
				sp1 = conn.setSavepoint();
				ArrayList<DBRecord> record3 = null;
				ArrayList<DBRecord> record4 = null;
				record3 = samplesDAO.query(conn, str2);
				iCode=stmt.executeUpdate(userSolution);
				System.err.println(iCode + "-----------");
				record4 = samplesDAO.query(conn, str2);
				score=0;
				if(record2.size()!=0) {
				Set<String>setProperty=record2.get(0).stringPropertyNames();
				String []stringProperty=new String[setProperty.size()];
				setProperty.toArray(stringProperty);
				for(int j=0;j<record2.size();j++) {
					for(int i=0;i<setProperty.size();i++) {
					System.out.println(record2.get(j).getField(stringProperty[i]));
					if(!record2.get(j).getField(stringProperty[i]).equals(record4.get(j).getField(stringProperty[i]))) {
						System.out.println("##");
					}
					}
				}
				}
				if(record2.equals(record4) || (record2.size()==0 && record4.size()==0)) {System.err.println("score=10"); score=10;}
				conn.rollback(sp1);
				if(score==0) {
					if (rule.equals("true")) {// 是否有辅助判断
						JSONObject jObj = JSON.parseObject(extraString);
						String extraSql = jObj.getString("sql");
						JSONArray jsonArray = JSON.parseArray(extraSql);
						for (int i = 0; i < jsonArray.size(); i++) {
							JSONObject Obj = jsonArray.getJSONObject(i);
							String halfAnsSql = Obj.getString("sql");
							int value = Obj.getInteger("value");
							if (extraJudge(conn, halfAnsSql, userSolution, extraType)) {
								score = value;
								break;
							} else {
								score = 0;
							}
						}
					} else {
						score = 0;
					}
					
				}
			}
			else if(extraType.equals("delete")) {
				String str1=deleteToSelect(solution);
				String str2=deleteToSelect(userSolution);
				ArrayList<DBRecord> record1 = null;
				ArrayList<DBRecord> record2 = null;
				record1 = samplesDAO.query(conn, str1);
				record2 = samplesDAO.query(conn, str2);
				score=0;
				if(record2.equals(record1)) {score=10;}
				conn.rollback(sp);
				if(score==0) {
					if (rule.equals("true")) {// 是否有辅助判断
						JSONObject jObj = JSON.parseObject(extraString);
						String extraSql = jObj.getString("sql");
						JSONArray jsonArray = JSON.parseArray(extraSql);
						for (int i = 0; i < jsonArray.size(); i++) {
							JSONObject Obj = jsonArray.getJSONObject(i);
							String halfAnsSql = Obj.getString("sql");
							int value = Obj.getInteger("value");
							if (extraJudge(conn, halfAnsSql, userSolution, extraType)) {
								score = value;
								break;
							} else {
								score = 0;
							}
						}
					} else {
						score = 0;
					}
					
				}
			}
			/*
			System.out.println("______________________________");
			String str1=updateToSelect(solution);
			String str2=updateToSelect(userSolution);
			System.out.println(str1);
			System.out.println(str2);
			System.out.println("______________________________");
			conn.setAutoCommit(false);// 通知数据库开启事务(start transaction)
			stmt = conn.createStatement();
			// 设置保存点，下面执行的在检查完成后都将回滚到此处的状态从而不改变数据库中的数据
			sp = conn.setSavepoint();
			ArrayList<DBRecord> record1 = null;
			ArrayList<DBRecord> record2 = null;
			record1 = samplesDAO.query(conn, str1);
			System.out.println("#1");
			//System.out.println(solution);
			iCode =stmt.executeUpdate(solution);
			System.err.println(iCode + "-----------");
			System.out.println("#2");
			record2 = samplesDAO.query(conn, str1);
			System.out.println("#3");
			conn.rollback(sp);
			
			Savepoint sp1 = null;
			sp1 = conn.setSavepoint();
			ArrayList<DBRecord> record3 = null;
			ArrayList<DBRecord> record4 = null;
			record3 = samplesDAO.query(conn, str1);
			iCode=stmt.executeUpdate(userSolution);
			System.err.println(iCode + "-----------");
			record4 = samplesDAO.query(conn, str1);
			score=0;
			
			Set<String>setProperty=record2.get(0).stringPropertyNames();
			String []stringProperty=new String[setProperty.size()];
			setProperty.toArray(stringProperty);
			for(int j=0;j<record2.size();j++) {
				for(int i=0;i<setProperty.size();i++) {
				System.out.println(record2.get(j).getField(stringProperty[i]));
				if(!record2.get(j).getField(stringProperty[i]).equals(record4.get(j).getField(stringProperty[i]))) {
					System.out.println("##");
				}
				}
			}
			if(record2.equals(record4)) {score=10;}
			conn.rollback(sp1);
			*/
			
			//System.out.println("====rollback===");
		} catch (java.sql.SQLException e) {
			//System.out.println("===rollback exception");
			score = 0;
			return score;
			//System.out.println("exception:" + e.getMessage());

		} finally {
			try {
				stmt.close();
				System.out.println("====conn.close end====");
				return score;
			} catch (SQLException e) {
				//System.out.println("====close exception====");
				//System.out.println("exception:" + e.getMessage());
			}
		}
		System.out.println("========update judge end=======");
		return score;
	}

	@Override
	public int tableJudge(Connection conn, String userSolution, String extraString) {
		int iCode = 0;
		Statement stmt = null;
		Savepoint sp = null;
		Integer score = 0;
		boolean exist = false;
		// Connection conn=null;
		System.out.println("========table judge start=======" + userSolution);
		// 该题目的extra信息 判分规则
		JSONObject jsonObj = JSON.parseObject(extraString);
		String extraType = jsonObj.getString("type");
		String tableAttribute = jsonObj.getString("attribute").toUpperCase();
		String tableName = jsonObj.getString("tableName").toUpperCase();

		try {
			// conn=dataSource.getConnection();
			conn.setAutoCommit(false);// 通知数据库开启事务(start transaction)
			stmt = conn.createStatement();
			exist = isTableOrViewExist(conn, tableName, "TABLE");
			System.out.println("before execute----" + exist);
			// 设置保存点，下面执行的在检查完成后都将回滚到此处的状态从而不改变数据库中的数据
			sp = conn.setSavepoint();
			// 根据具体不同的那一道题目进行不同的处理
			if (extraType.equals("create")) {
				iCode = stmt.executeUpdate(userSolution);// 执行创建语句
				exist = isTableOrViewExist(conn, tableName.toUpperCase(), "TABLE");
				if (exist) {
					score = isColumnExist(conn, tableName.toUpperCase(), tableAttribute) ? 10 : 5;
					// System.out.println(score+"");
				} else {
					score = 0;
				}
			} else if (extraType.equals("alter")) {
				iCode = stmt.executeUpdate(userSolution);// 执行创建语句
				score = isColumnAccord(conn, tableName, tableAttribute) ? 10 : 0;
			}
			conn.rollback(sp);
		} catch (java.sql.SQLException e) {
			System.out.println("语法不正确");
			score = 0;
			// e.printStackTrace();
			System.out.println("exception:" + e.getMessage());

		} finally {
			try {

				exist = isTableOrViewExist(conn, tableName, "TABLE");
				stmt.close();
				// conn.close();
			} catch (SQLException e) {
				// e.printStackTrace();
				System.out.println("exception:" + e.getMessage());
			}
		}
		System.out.println("========table judge end=======");
		return score;
	}

	@Override
	public int indexJudge(Connection conn, String userSolution, String extraString) {
		int iCode = 0;
		Statement stmt = null;
		Savepoint sp = null;
		Integer score = 0;
		boolean exist = false;
		ArrayList<String> indexList = new ArrayList<String>();
		ArrayList<String> orderList = new ArrayList<String>();
		// Connection conn=null;
		System.out.println("========index judge start======" + userSolution);
		// 该题目的extra信息 判分规则
		JSONObject jsonObj = JSON.parseObject(extraString);
		String extraType = jsonObj.getString("type");
		String index = jsonObj.getString("index");
		String tableName = jsonObj.getString("tableName");
		JSONArray columnArray = JSON.parseArray(index);
		try {
			// conn=dataSource.getConnection();
			conn.setAutoCommit(false);// 通知数据库开启事务(start transaction)
			stmt = conn.createStatement();
			// 设置保存点，下面执行的在检查完成后都将回滚到此处的状态从而不改变数据库中的数据
			sp = conn.setSavepoint();
			// 根据具体不同的那一道题目进行不同的处理
			if (extraType.equals("create")) {
				iCode = stmt.executeUpdate(userSolution);// 执行创建语句
				System.err.println(iCode);
				indexList = getIndexList(conn, tableName);
				//orderList = getOrderList(conn, tableName);
				//System.err.println("##############");
				//System.err.println(orderList.get(0));
				//System.err.println("##############");
				
				System.err.println(columnArray.getString(0));
				System.err.println(indexList.size());
				if(indexList.size()==columnArray.size()) {
				for(int i=0;i<indexList.size();i++) {
					if(!indexList.contains(columnArray.getString(i).toUpperCase())) {
						score=0;
					}
				}
				score=10;
				}
				
				/*
				if (indexList.size() == 1 && indexList.contains(index) ) {
					score = 10;
				}*/
			}
			conn.rollback(sp);
		} catch (java.sql.SQLException e) {
			System.out.println("语法不正确");
			score = 0;
			// e.printStackTrace();
			System.out.println("exception:" + e.getMessage());

		} finally {
			try {

				stmt.close();
				// conn.close();
			} catch (SQLException e) {
				// e.printStackTrace();
				System.out.println("exception:" + e.getMessage());
			}
		}
		System.out.println("========index judge end=======");
		return score;

	}

	@Override
	public int viewJudge(Connection conn, String userSolution, String extraString) {

		int iCode = 0;
		Statement stmt = null;
		Savepoint sp = null;
		Integer score = 0;
		boolean exist = false;
		// Connection conn=null;
		System.out.println("========view judge start=======" + userSolution);
		// 该题目的extra信息 判分规则
		JSONObject jsonObj = JSON.parseObject(extraString);
		String extraType = jsonObj.getString("type");
		String tableAttribute = jsonObj.getString("attribute").toUpperCase();
		String viewName = jsonObj.getString("viewName").toUpperCase();

		try {
			// conn=dataSource.getConnection();
			conn.setAutoCommit(false);// 通知数据库开启事务(start transaction)
			stmt = conn.createStatement();
			exist = isTableOrViewExist(conn, viewName, "VIEW");
			System.out.println("before execute----" + exist);
			// 设置保存点，下面执行的在检查完成后都将回滚到此处的状态从而不改变数据库中的数据
			sp = conn.setSavepoint();
			// 根据具体不同的那一道题目进行不同的处理
			if (extraType.equals("create")) {
				iCode = stmt.executeUpdate(userSolution);// 执行创建语句
				exist = isTableOrViewExist(conn, viewName, "VIEW");
				if (exist) {
					score = isColumnExist(conn, viewName, tableAttribute) ? 10 : 5;
					// System.out.println(score+"");
				} else {
					score = 0;
				}
			}
			conn.rollback(sp);
		} catch (java.sql.SQLException e) {
			System.out.println("语法不正确");
			score = 0;
			// e.printStackTrace();
			System.out.println("exception:" + e.getMessage());
		} finally {
			try {

				exist = isTableOrViewExist(conn, viewName, "VIEW");
				stmt.close();
				// conn.close();
			} catch (SQLException e) {
				// e.printStackTrace();
				System.out.println("exception:" + e.getMessage());
			}
		}
		System.out.println("========view judge end=======");
		return score;
	}

	@Override
	public int entityJudge(Connection conn, String userSolution, String extraString) {
		int iCode = 0;
		Statement stmt = null;
		Savepoint sp = null;
		Integer score = 0;
		boolean exist = false;
		boolean isKeyAllAccord = true;
		// Connection conn=null;

		// 该题目的extra信息 判分规则
		JSONObject jsonObj = JSON.parseObject(extraString);
		String extraType = jsonObj.getString("type");
		String tableAttribute = jsonObj.getString("attribute").toUpperCase();
		String tableName = jsonObj.getString("tableName").toUpperCase();
		String keyString = jsonObj.getString("key").toUpperCase();
		String existString = jsonObj.getString("exist").toUpperCase();
		System.out.println("========entity judge start=======" + userSolution);
		try {
			// conn=dataSource.getConnection();
			conn.setAutoCommit(false);// 通知数据库开启事务(start transaction)
			stmt = conn.createStatement();
			// 设置保存点，下面执行的在检查完成后都将回滚到此处的状态从而不改变数据库中的数据
			sp = conn.setSavepoint();
			iCode = stmt.executeUpdate(userSolution);// 执行语句
			if (existString.equals("false")) {// 不存在表，自己建表并添加
				exist = isTableOrViewExist(conn, tableName, "TABLE");
				if (exist) {// 建好了表了 ；+2分
					score = score + 2;
					exist = isColumnExist(conn, tableName, tableAttribute);
					if (exist) {// 属性也存在 ;+2分
						score = score + 2;
						ArrayList<String> keyList = new ArrayList<String>();
						JSONArray keyArray = JSON.parseArray(keyString);
						keyList = getAllPrimaryKeys(conn, tableName);
						if (keyArray.size() == keyList.size()) {// 根要求的键数一样
							for (int i = 0; i < keyList.size(); i++) {
								if (!keyList.contains(keyArray.get(i))) {
									isKeyAllAccord = false;
								}
							}
							score = isKeyAllAccord ? 10 : score;
						}
					}
				}
			} else {
				ArrayList<String> keyList = new ArrayList<String>();
				JSONArray keyArray = JSON.parseArray(keyString);
				keyList = getAllPrimaryKeys(conn, tableName);
				if (keyArray.size() == keyList.size()) {// 根要求的键数一样
					for (int i = 0; i < keyList.size(); i++) {
						if (!keyList.contains(keyArray.get(i))) {
							isKeyAllAccord = false;
						}
					}
					score = isKeyAllAccord ? 10 : 0;
				}
			}
			conn.rollback(sp);
		} catch (java.sql.SQLException e) {
			score = 0;
			// e.printStackTrace();
			System.out.println("exception:" + e.getMessage());

		} finally {
			try {

				stmt.close();
				// conn.close();
			} catch (SQLException e) {
				// e.printStackTrace();
				System.out.println("exception:" + e.getMessage());
			}
		}
		System.out.println("========entity judge end=======");
		return score;
	}

	@Override
	public int referJudge(Connection conn, String userSolution, String extraString) {
		int iCode = 0;
		Statement stmt = null;
		Savepoint sp = null;
		Integer score = 0;
		boolean exist = false;
		boolean isKeyAllAccord = true;
		// Connection conn=null;
		System.out.println("========refer judge start======" + userSolution);
		// 该题目的extra信息 判分规则
		JSONObject jsonObj = JSON.parseObject(extraString);
		String extraType = jsonObj.getString("type");
		String tableAttribute = jsonObj.getString("attribute");
		String tableName = jsonObj.getString("tableName");
		String exportedKeyString = jsonObj.getString("exportedKey");
		String existString = jsonObj.getString("exist");

		try {
			// conn=dataSource.getConnection();
			conn.setAutoCommit(false);// 通知数据库开启事务(start transaction)
			stmt = conn.createStatement();
			// 设置保存点，下面执行的在检查完成后都将回滚到此处的状态从而不改变数据库中的数据
			sp = conn.setSavepoint();
			stmt.executeUpdate("CREATE UNIQUE INDEX Cno_index ON Course(Cno)");
			iCode = stmt.executeUpdate(userSolution);// 执行语句
			System.err.println("icode--" + iCode);
			ArrayList<String> keyList = new ArrayList<String>();
			JSONArray keyArray = JSON.parseArray(exportedKeyString);
			keyList = getAllExportedKeys(conn, tableName);
			if (keyArray.size() == keyList.size()) {// 根要求的键数一样
				for (int i = 0; i < keyList.size(); i++) {
					if (!keyList.contains(keyArray.get(i))) {
						isKeyAllAccord = false;
					}
				}
				score = isKeyAllAccord ? 10 : 0;
			}
			conn.rollback(sp);
		} catch (java.sql.SQLException e) {
			score = 0;
			// e.printStackTrace();
			System.out.println("exception:" + e.getMessage());

		} finally {
			try {

				stmt.close();
				// conn.close();
			} catch (SQLException e) {
				// e.printStackTrace();
				System.out.println("exception:" + e.getMessage());
			}
		}
		System.out.println("========fefer judge end=======");
		return score;

	}

	/**
	 * 判断两条查询语句的结果集是否相同
	 * 
	 * @param sql1
	 * @param sql2
	 * @param selectType
	 *            查询的类型，如果有排序的查询，则不能用minus进行比较结果集，通过遍历的方法进行逐行比较
	 * @return true表示查询结果集相同 false为不同
	 * @author cyj
	 */
	public boolean isResultSetEqual(Connection conn, String sql1, String sql2, String selectType) {
		boolean isEqual = true;
		List<DBRecord> recordList1 = null;
		List<DBRecord> recordList2 = null;
		DBRecord record1 = null;
		DBRecord record2 = null;

		if (selectType.equals("select_normal")) {

			String spliceSql1 = " ( " + sql1 + " ) " + " minus all" + " ( " + sql2 + " ) ";
			String spliceSql2 = " ( " + sql2 + " ) " + " minus all" + " ( " + sql1 + " ) ";

			System.out.println("select_normal  " + spliceSql1);
			System.out.println("select_normal  " + spliceSql2);

			try {

				recordList1 = samplesDAO.query(conn, spliceSql1);
				recordList2 = samplesDAO.query(conn, spliceSql2);
				System.out.println(recordList1.size()+"@"+recordList2.size());
				isEqual = (recordList1.size() == 0 && recordList2.size() == 0) ? true : false;
			} catch (Exception e) {
				// e.printStackTrace();//
				System.out.println("isResultSetEqual has exception!!!");
				isEqual = false;
			}
		} else {
			try {
				recordList1 = samplesDAO.query(conn, sql1);
				recordList2 = samplesDAO.query(conn, sql2);
				for (int i = 0; i < recordList1.size(); i++) {
					record1 = recordList1.get(i);
					record2 = recordList2.get(i);
					String recordString1 = record1.toString();
					String recordString2 = record2.toString();
					if (!recordString1.equals(recordString2)) {
						isEqual = false;
						//System.out.println(recordString1 + "|" + recordString2);
					}
				} // for循环结束
			} catch (Exception e) {
				// e.printStackTrace();
				System.out.println("isResultSetEqual has exception!!!22");
				return false;
			}
		} // else结束

		return isEqual;
	}

	/**
	 * 判断某个表或视图是否在该数据库总
	 * 
	 * @param name
	 *            要判断的表或视图的名字
	 * @param type
	 *            TABLE ,VIEW
	 * @return
	 */
	public boolean isTableOrViewExist(Connection conn, String name, String type) {
		ArrayList<String> tableOrViewLists = new ArrayList<String>();
		tableOrViewLists = getTableOrViewList(conn, type);
		return tableOrViewLists.contains(name);
	}

	/**
	 * 判断某一些列属性是否在该表
	 * 
	 * @param tableName
	 * @param tableAttribute
	 * @return
	 */
	private boolean isColumnExist(Connection conn, String tableName, String tableAttribute) {
		boolean exist = true;
		ArrayList<String> columns = new ArrayList<String>();
		columns = getTableColumns(conn, tableName);
		JSONArray columnArray = JSON.parseArray(tableAttribute);
		for (int i = 0; i < columnArray.size(); i++) {
			if (!columns.contains(columnArray.getString(i)) || columnArray.size() != columns.size()) {
				exist = false;
			}
		}
		return exist;
	}

	/**
	 * 判断修改后的表的列是否符合要求:类型、大小、名字
	 * 
	 * @param tableName
	 * @param tableAttribute
	 * @return
	 */
	private boolean isColumnAccord(Connection conn, String tableName, String tableAttribute) {
		boolean accord = false;
		HashMap<String, String> columnMap = new HashMap<String, String>();
		JSONObject object = null;
		JSONArray tableColumns = JSON.parseArray(tableAttribute);
		ArrayList<String> columns = new ArrayList<String>();
		columns = getTableColumns(conn, tableName);// 该表此时的列属性

		for (int i = 0; i < tableColumns.size(); i++) {
			object = JSON.parseObject(tableColumns.getString(i));
			String colName = object.getString("columnName").toUpperCase();
			String colType = object.getString("columnType").toUpperCase();
			int colSize = object.getIntValue("columnSize");
			if (columns.contains(colName)) {// 该列已经添加进表
				columnMap = getTableColumnsInfo(conn, tableName, colName);
				// 判断该列的类型 和 大小是否符合
				if (columnMap.get("columnType").equals(colType)
						&& Integer.parseInt(columnMap.get("columnSize")) == colSize) {
					accord = true;
				}
			}
		}
		return accord;
	}

	/**
	 * 获取指定列表中指定列的信息
	 * 
	 * @param tableName
	 * @param columnName
	 * @return
	 */
	public HashMap<String, String> getTableColumnsInfo(Connection conn, String tableName, String columnName) {
		DatabaseMetaData dbMetaData = null;
		try {
			dbMetaData = conn.getMetaData();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		HashMap<String, String> columMap = new HashMap<String, String>();
		try {
			ResultSet rs = dbMetaData.getColumns(null, "PUBLIC", tableName, "%");
			while (rs.next()) {
				String tempColumnName = rs.getString("COLUMN_NAME"); // 列名
				String columnType = rs.getString("TYPE_NAME"); // 列数据类型
				int columnSize = rs.getInt("COLUMN_SIZE"); // 列大小
				String columnNull = rs.getString("IS_NULLABLE"); // 是否可以为空 【NO
																	// YES】
				if (tempColumnName.equals(columnName)) {
					columMap.put("columnName", columnName);
					columMap.put("columnType", columnType);
					columMap.put("columnSize", columnSize + "");
					columMap.put("columnNull", columnNull);
				}
			}
		} catch (SQLException e) {
			// e.printStackTrace();
			System.out.println("exception:" + e.getMessage());
		}
		return columMap;
	}

	/**
	 * 获取数据库的所有表或所有视图
	 * 
	 * @param type
	 *            VIEW ,TABLE
	 * @author cyj
	 */
	public ArrayList<String> getTableOrViewList(Connection conn, String type) {

		DatabaseMetaData dbMetaData = null;
		try {
			dbMetaData = conn.getMetaData();
		} catch (SQLException e1) {
			// e1.printStackTrace();
			System.out.println("exception:" + e1.getMessage());
		}
		ArrayList<String> tableLists = new ArrayList<String>();
		ResultSet rs = null;

		String[] types = { type };
		try {
			rs = dbMetaData.getTables(null, "PUBLIC", "%", types);
			while (rs.next()) {
				String tableName = rs.getString("TABLE_NAME"); // 表名
				tableLists.add(tableName);
				String tableType = rs.getString("TABLE_TYPE"); // 表类型
				String remarks = rs.getString("REMARKS"); // 表备注
				System.out.println(tableName + " - " + tableType + " - " + remarks);

			}
		} catch (SQLException e) {
			// e.printStackTrace();
			System.out.println("exception:" + e.getMessage());
		}
		return tableLists;
	}

	/**
	 * 获得表或视图中的所有列信息
	 */
	public ArrayList<String> getTableColumns(Connection conn, String tableName) {
		DatabaseMetaData dbMetaData = null;
		try {
			dbMetaData = conn.getMetaData();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		ArrayList<String> columns = new ArrayList<String>();
		try {
			ResultSet rs = dbMetaData.getColumns(null, "PUBLIC", tableName, "%");
			while (rs.next()) {
				String columnName = rs.getString("COLUMN_NAME"); // 列名
				columns.add(columnName);
			}
		} catch (SQLException e) {
			// e.printStackTrace();
			System.out.println("exception:" + e.getMessage());
		}
		return columns;
	}

	/**
	 * 获取该表所有的主键
	 * 
	 * @param tableName
	 * @return
	 */
	public ArrayList<String> getAllPrimaryKeys(Connection conn, String tableName) {
		DatabaseMetaData dbMetaData = null;
		try {
			dbMetaData = conn.getMetaData();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		ArrayList<String> keyLists = new ArrayList<String>();
		try {
			ResultSet rs = dbMetaData.getPrimaryKeys(null, "PUBLIC", tableName);
			while (rs.next()) {
				String columnName = rs.getString("COLUMN_NAME"); // 主键名字
				keyLists.add(columnName);
				short keySeq = rs.getShort("KEY_SEQ");
				String pkName = rs.getString("PK_NAME");
				System.out.println(columnName + "-" + columnName + "-" + keySeq + "-" + pkName);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return keyLists;
	}

	/**
	 * 获得一个表的外键信息
	 * 
	 * @param tableName
	 * @return
	 * @author: cyj
	 * @date: 2017年3月23日 下午11:30:18
	 */
	public ArrayList<String> getAllExportedKeys(Connection conn, String tableName) {
		DatabaseMetaData dbMetaData = null;
		try {
			dbMetaData = conn.getMetaData();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		ArrayList<String> exportedKeyList = new ArrayList<String>();
		try {
			ResultSet rs = dbMetaData.getImportedKeys(null, "PUBLIC", tableName);
			while (rs.next()) {
				String fkColumnName = rs.getString("FKCOLUMN_NAME");
				String pkTablenName = rs.getString("PKTABLE_NAME");
				String pkColumnName = rs.getString("PKCOLUMN_NAME");
				System.out.println(fkColumnName + "-" + pkColumnName + "-" + pkTablenName + "-");
				exportedKeyList.add(fkColumnName);
			}
		} catch (SQLException e) {
			System.out.println("exception:" + e.getMessage());
		}
		return exportedKeyList;
	}

	/**
	 * 获取该表的索引
	 * 
	 * @param tableName
	 * @return
	 */
	public ArrayList<String> getIndexList(Connection conn, String tableName) {
		DatabaseMetaData dbMetaData = null;
		try {
			dbMetaData = conn.getMetaData();
		} catch (SQLException e1) {
			System.out.println("exception:" + e1.getMessage());
		}
		ArrayList<String> indexLists = new ArrayList<String>();
		try {
			ResultSet rs = dbMetaData.getIndexInfo(null, "PUBLIC", tableName.toUpperCase(), true, true);
			System.err.println("##1#"+tableName);
			while (rs.next()) {
				System.err.println("##1");
				boolean nonUnique = rs.getBoolean("NON_UNIQUE");
				String indexQualifier = rs.getString("INDEX_QUALIFIER");
				String indexName = rs.getString("INDEX_NAME");
				short type = rs.getShort("TYPE");
				short ordinalPosition = rs.getShort("ORDINAL_POSITION");
				String columnName = rs.getString("COLUMN_NAME");
				String ascOrDesc = rs.getString("ASC_OR_DESC");
				int cardinality = rs.getInt("CARDINALITY");
				indexLists.add(columnName);//需要修改
				System.out.println(nonUnique + "-" + indexQualifier + "-" + indexName + "-" + type + "-"
						+ ordinalPosition + "-" + columnName + "-" + ascOrDesc + "-" + cardinality);
			}
		} catch (SQLException e) {
			System.out.println("exception:" + e.getMessage());
		}
		return indexLists;
	}
	/*
	public ArrayList<String> getOrderList(Connection conn, String tableName) {//新增
		DatabaseMetaData dbMetaData = null;
		try {
			dbMetaData = conn.getMetaData();
		} catch (SQLException e1) {
			System.out.println("exception:" + e1.getMessage());
		}
		ArrayList<String> indexLists = new ArrayList<String>();
		try {
			ResultSet rs = dbMetaData.getIndexInfo(null, "PUBLIC", tableName, true, true);
			while (rs.next()) {
				boolean nonUnique = rs.getBoolean("NON_UNIQUE");
				String indexQualifier = rs.getString("INDEX_QUALIFIER");
				String indexName = rs.getString("INDEX_NAME");
				short type = rs.getShort("TYPE");
				short ordinalPosition = rs.getShort("ORDINAL_POSITION");
				String columnName = rs.getString("COLUMN_NAME");
				String ascOrDesc = rs.getString("ASC_OR_DESC");
				int cardinality = rs.getInt("CARDINALITY");
				indexLists.add(ascOrDesc);//需要修改
				System.out.println(nonUnique + "-" + indexQualifier + "-" + indexName + "-" + type + "-"
						+ ordinalPosition + "-" + columnName + "-" + ascOrDesc + "-" + cardinality);
			}
		} catch (SQLException e) {
			System.out.println("exception:" + e.getMessage());
		}
		return indexLists;
	}
	*/
	@Override
	public int saveProcessJudge(Connection conn, String userSolution, String extraString) {
		int iCode = 0;
		Statement stmt = null;
		Savepoint sp = null;
		Integer score = 0;
		boolean exist = false;
		// Connection conn=null;

		// 该题目的extra信息 判分规则
		JSONObject jsonObj = JSON.parseObject(extraString);
		String extraType = jsonObj.getString("type");
		String executeSql = jsonObj.getString("execute");
		String checkSql = jsonObj.getString("sql");
		int value = jsonObj.getIntValue("value");

		try {
			// conn=dataSource.getConnection();
			conn.setAutoCommit(false);// 通知数据库开启事务(start transaction)
			stmt = conn.createStatement();
			String tvalue = samplesDAO.queryOne(conn, checkSql);
			System.out.println("before execute----" + value);
			// 设置保存点，下面执行的在检查完成后都将回滚到此处的状态从而不改变数据库中的数据
			sp = conn.setSavepoint();

			// 根据具体不同的那一道题目进行不同的处理
			if (extraType.equals("procedure")) {
				boolean iiCode = stmt.execute(userSolution);// 执行创建语句
				tvalue = samplesDAO.queryOne(conn, checkSql);
				CallableStatement css = conn.prepareCall("{call proc_stu()}");
				css.execute();
				css.close();
				tvalue = samplesDAO.queryOne(conn, checkSql);
				System.out.println("before execute----" + value);
				score = Integer.parseInt(tvalue) == value ? 10 : 0;
			} else if (extraType.equals("procedure_params")) {
				iCode = stmt.executeUpdate(userSolution);// 执行创建语句
				tvalue = samplesDAO.queryOne(conn, checkSql);
				CallableStatement cs = conn.prepareCall("{call proc_sc(?)}");
				cs.setString(1, "2017004004");
				cs.execute();
				cs.close();
				tvalue = samplesDAO.queryOne(conn, checkSql);
				score = Integer.parseInt(tvalue) == value ? 10 : 0;
			}
			conn.rollback(sp);
		} catch (java.sql.SQLException e) {
			System.out.println("语法不正确");
			score = 0;
			// e.printStackTrace();
			System.out.println("exception:" + e.getMessage());

		} finally {
			try {

				String valueString = samplesDAO.queryOne(conn, checkSql);
				stmt.close();
				// conn.close();
			} catch (SQLException e) {
				// e.printStackTrace();
				System.out.println("exception:" + e.getMessage());
			}
		}
		return score;
	}

	@Override
	public int triggerJudge(Connection conn, String userSolution, String extraString) {
		int iCode = 0;
		Statement stmt = null;
		Savepoint sp = null;
		Integer score = 0;
		boolean exist = false;
		// Connection conn=null;

		// 该题目的extra信息 判分规则
		JSONObject jsonObj = JSON.parseObject(extraString);
		String extraType = jsonObj.getString("type");
		String checkSql = jsonObj.getString("sql");
		int value = jsonObj.getIntValue("value");
		String tvalue = "";

		try {
			// conn=dataSource.getConnection();
			conn.setAutoCommit(false);// 通知数据库开启事务(start transaction)
			stmt = conn.createStatement();
			// 设置保存点，下面执行的在检查完成后都将回滚到此处的状态从而不改变数据库中的数据
			sp = conn.setSavepoint();

			// 根据具体不同的那一道题目进行不同的处理
			if (extraType.equals("trigger1")) {
				String sql1 = "create table stu (sno char(10),sname char(10),sage int)";
				stmt.executeUpdate(sql1);
				String sql2 = "insert into stu values('001','mark',20)";
				stmt.executeUpdate(sql2);
				iCode = stmt.executeUpdate(userSolution);// 执行创建语句
				String sql3 = "update stu set sage=19";
				stmt.executeUpdate(sql3);
				tvalue = samplesDAO.queryOne(conn, checkSql);
				System.out.println("before execute----" + value);
				score = Integer.parseInt(tvalue) == value ? 10 : 0;
			} else if (extraType.equals("trigger2")) {
				String sql1 = "create table stus(sno char(10),coursenums int ,credit int)";
				stmt.executeUpdate(sql1);
				String sql2 = "insert into stus values('001',10,20)";
				stmt.executeUpdate(sql2);
				iCode = stmt.executeUpdate(userSolution);// 执行创建语句
				String tvalue0 = samplesDAO.queryOne(conn, checkSql);
				String sql3 = "update stus set coursenums = coursenums+2";
				stmt.executeUpdate(sql3);
				tvalue = samplesDAO.queryOne(conn, checkSql);
				score = (Integer.parseInt(tvalue) - Integer.parseInt(tvalue0)) == value ? 10 : 0;
			}
			conn.rollback(sp);
		} catch (java.sql.SQLException e) {
			System.out.println("语法不正确");
			System.out.println(e.getMessage());
			String errorMessage = e.getMessage().toLowerCase();
			score = errorMessage.indexOf("wrongupdate") > 0 ? 10 : 0;
			// e.printStackTrace();
			System.out.println("exception:" + e.getMessage());

		} finally {
			try {

				stmt.close();
				// conn.close();
			} catch (SQLException e) {
				// e.printStackTrace();
				System.out.println("exception:" + e.getMessage());
			}
		}
		return score;

	}

	@Override
	public int RollbackJudge(Connection conn, String userSolution, String extraString) {
		// int iCode = 0;
		// Connection conn=null;
		Statement stmt = null;
		Savepoint sp = null;
		Integer score = 0;
		boolean isdelete = false;
		boolean isOK = false;
		boolean isRollback = false;
		System.out.println("======== rollback start=======" + userSolution);
		// 该题目的extra信息 判分规则
		JSONObject jsonObj = JSON.parseObject(extraString);
		String extraSql = jsonObj.getString("sql");
		System.out.println(extraSql + "......");
		Integer extraValue = jsonObj.getInteger("value");
		String extraType = jsonObj.getString("type");

		try {
			// conn=dataSource.getConnection();
			conn.setAutoCommit(false);// 通知数据库开启事务(start transaction)
			stmt = conn.createStatement();
			String value = samplesDAO.queryOne(conn, extraSql);
			System.out.println("before execute----" + value);
			// 设置保存点，下面执行的在检查完成后都将回滚到此处的状态从而不改变数据库中的数据
			sp = conn.setSavepoint();
			System.out.println(sp);
			// 根据具体不同的那一道题目进行不同的处理

			if (extraType.equals("rollback")) {

				System.out.println("////rollback");
				System.out.println(stmt + "---");
				// String str = new
				// String(userSolution.getBytes("iso8859-1"),"GBK");
				// stmt.executeUpdate(userSolution);
				String solutions[] = userSolution.split(";");
				if (solutions.length == 3) {
					System.out.println("----------0");
					stmt.execute(solutions[0] + ";");// 执行创建语句
					System.out.println(solutions[0] + "----------");
					stmt.execute(solutions[1] + ";");
					System.out.println(solutions[1] + "----------");
					value = samplesDAO.queryOne(conn, extraSql);
					if (value == null) {
						System.out.println("----------1");
						isdelete = true;
					}
					stmt.execute(solutions[2] + ";");
					isRollback = solutions[2].contains("rollback") || solutions[2].contains("ROLLback");
					System.out.println(solutions[2] + "----------");
					value = samplesDAO.queryOne(conn, extraSql);
					if (value != null) {
						System.out.println("----------2");
						isOK = true;
					}
					System.out.println("----------3");
					if (isOK && isdelete) {
						// 查询结果为空。说明答案错误
						score = 10;
						System.out.println("----------4");
					} else {
						score = 0;
						System.out.println("----------5");
					}
				} else {
					score = 0;
				}

			}
			System.out.println("----------6");
			if (!isRollback) {
				conn.rollback(sp);
			}
			System.out.println("====rollback===");
		} catch (java.sql.SQLException e) {
			System.out.println("===rollback exception");
			score = 0;
			System.out.println("exception:" + e.getMessage());

		} finally {
			try {
				stmt.close();
				System.out.println("====conn.close end====");
			} catch (SQLException e) {
				System.out.println("====close exception====");
				System.out.println("exception:" + e.getMessage());
			}
		}
		System.out.println("========rollback judge end=======");
		return score;
	}

	@Override
	public int authJudge(Connection conn, String userSolution, String extraString) {
		int iCode = 0;
		Statement stmt = null;
		Savepoint sp = null;
		Integer score = 0;
		boolean exist = false;

		// 该题目的extra信息 判分规则
		JSONObject jsonObj = JSON.parseObject(extraString);
		String extraType = jsonObj.getString("type");
		String userName = jsonObj.getString("user");
		String tableName = jsonObj.getString("tableName");
		String checkSql = jsonObj.getString("sql");
		String authList = jsonObj.getString("authList");
		int value = jsonObj.getIntValue("value");
		String tvalue = "";

		try {
			conn.setAutoCommit(false);// 通知数据库开启事务(start transaction)
			stmt = conn.createStatement();
			// 设置保存点，下面执行的在检查完成后都将回滚到此处的状态从而不改变数据库中的数据
			sp = conn.setSavepoint();

			// 根据具体不同的那一道题目进行不同的处理
			boolean isUserAuthed = false;
			if (extraType.equals("auth")) {
				System.out.println("////auth");
				System.out.println(stmt + "---");
				// isUserAuthed = isUserAuthed(conn, checkSql, userName,
				// tableName, authList);
				stmt.executeUpdate(userSolution);
				// stmt.execute(userSolution);//执行创建语句
				System.out.println("auth mid");
				isUserAuthed = isUserAuthed(conn, checkSql, userName, tableName, authList);
				System.out.println("///auth end");
				score = isUserAuthed ? 10 : 0;
			} else if (extraType.equals("auth3")) {
				String sql2 = "grant select,update on table student to u1";
				stmt.executeUpdate(sql2);
				iCode = stmt.executeUpdate(userSolution);// 执行创建语句

				boolean isUserAuthedRevoked = isUserAuthedRevoked(conn, checkSql, userName, tableName, authList);
				// 如果没有了select权限，说明做对了
				score = isUserAuthedRevoked ? 10 : 0;
			}
			// conn.rollback(sp);
		} catch (java.sql.SQLException e) {
			System.out.println("exception:" + e.getMessage());
		} finally {
			try {
				stmt.close();
			} catch (SQLException e) {
				System.out.println("exception:" + e.getMessage());
			}
		}
		return score;
	}

	/**
	 * 判断指定的用户是否已经有了指定的权限
	 * 
	 * @param conn
	 * @param sql
	 * @param userName
	 * @param tableName
	 * @param authList
	 * @return
	 * @author: cyj
	 * @date: 2017年4月8日 下午2:47:40
	 */
	private boolean isUserAuthed(Connection conn, String sql, String userName, String tableName, String authList) {
		boolean isUserAuthed = true;
		try {

			ArrayList<DBRecord> records = samplesDAO.query(conn, sql);
			if (tableName.equals("NONE")) {// 第二题 创建用户权限
				if (records.size() == 1 && records.get(0).getString("USENAME").equals("U2")
						&& records.get(0).getString("USECREATEROLE").equals("true")) {
					isUserAuthed = true;
				} else {
					isUserAuthed = false;
				}
			} else {
				if (records.size() > 0) {// 有查到权限

					ArrayList<String> authrecords = new ArrayList<String>();// 学生赋予的权限
					for (int i = 0; i < records.size(); i++) {
						if (records.get(i).getString("GRANTEE").equals(userName)
								&& records.get(i).getString("OBJECT_NAME").equals(tableName)) {
							authrecords.add(records.get(i).getString("PRIVILEGE_TYPE"));
						}

					}
					// 规则里的信息
					JSONArray jsonArray = JSON.parseArray(authList);
					if (jsonArray.size() == authrecords.size()) {
						for (int i = 0; i < jsonArray.size(); i++) {
							String authName = jsonArray.getString(i);
							if (authrecords.contains(authName)) {
								continue;
							} else {
								isUserAuthed = false;
							}
						}
					} else {
						isUserAuthed = false;
					}
				} else {
					isUserAuthed = false;
				}

			} // else end

		} catch (SQLException e) {
			// e.printStackTrace();
			isUserAuthed = false;
			System.out.println("isuserauth exception" + e.getMessage());
		}

		return isUserAuthed;
	}

	/**
	 * 用户权限是否已被撤销
	 * 
	 * @param conn
	 * @param sql
	 * @param userName
	 * @param tableName
	 * @param authList
	 * @return
	 * @author: cyj
	 * @date: 2017年4月8日 下午3:12:36
	 */
	private boolean isUserAuthedRevoked(Connection conn, String sql, String userName, String tableName,
			String authList) {
		boolean isUserAuthedRevoked = true;
		try {
			ArrayList<DBRecord> records = samplesDAO.query(conn, sql);
			if (records.size() > 0) {// 有查到权限

				ArrayList<String> authrecords = new ArrayList<String>();// 学生赋予的权限
				for (int i = 0; i < records.size(); i++) {
					if (!tableName.equals("NONE")) {
						if (records.get(i).getString("GRANTEE").equals(userName)
								&& records.get(i).getString("OBJECT_NAME").equals(tableName)) {
							authrecords.add(records.get(i).getString("PRIVILEGE_TYPE"));
						}
					}
				}
				// 规则里的信息
				JSONArray jsonArray = JSON.parseArray(authList);
				for (int i = 0; i < authrecords.size(); i++) {
					String authName = jsonArray.getString(i);
					if (authrecords.contains(authName)) {
						isUserAuthedRevoked = false;
					} else {
						continue;
					}
				}
			} else {
				isUserAuthedRevoked = false;
			}
		} catch (SQLException e) {
			// e.printStackTrace();
			isUserAuthedRevoked = false;
			System.out.println("isuserauth exception" + e.getMessage());
		}

		return isUserAuthedRevoked;
	}
	private String updateToSelect(String solution) {
		String str1=solution;
		String str2=solution;
		char ch;
		int count=0;
		System.out.println("++++++++++++++1");
		int wheresit=str1.indexOf("where");
		System.out.println(wheresit);
		for(int i=0;i<str1.length();i++){
			ch=str1.charAt(i);
			if(ch=='(')count++;
			if(ch==')')count--;
			if(wheresit==i){
				if(count==0)break;
				else {wheresit=(str1.substring(i+1)).indexOf("where")+wheresit+1;}
			}
		}
		System.out.println("++++++++++++++2");
		if(wheresit==-1) {str1="";wheresit=solution.length();}
		else str1=str1.substring(wheresit);
		
		int setcount=0;
		String strSet="";
		for(int i=str2.indexOf(" set ")+4;i<wheresit;i++) {
			ch=str2.charAt(i);
			if(ch==' ') {continue;}
			if(ch=='=') {setcount=1;}
			if(ch==',') {setcount=0;}
			if(setcount==0) {strSet=strSet+ch;}
		}
		//str1="select * from "+solution.substring(solution.indexOf("update ")+7,solution.indexOf("set"))+str1;
		//System.out.println("++++++++++++++");
		str1="select "+strSet+" from "+solution.substring(solution.indexOf("update ")+7,solution.indexOf("set"))+str1;
		//System.out.println(strSet);
		//System.out.println("++++++++++++++");
		return str1;
	}
	private String insertToSelect(String solution) {
		String str1=solution;
		char ch;
		String strTable="";
		int valsit=str1.indexOf(" values");
		if(valsit<1)valsit=str1.length();
		for(int i=str1.indexOf(" into ")+5;i<valsit;i++){
			ch=str1.charAt(i);
			if(ch=='(') {break;}
			strTable=strTable+ch;
		}
		str1="select * from "+strTable;
		return str1;
	}
	private String deleteToSelect(String solution) {
		String str1="";
		str1="select * "+solution.substring(solution.indexOf("from "));;
		return str1;
	}
	private boolean extraJudge(Connection conn, String extraSql, String userSolution,  String selectType) throws SQLException {
		int iCode;
		Statement stmt = null;
		Savepoint sp = null;
		if(selectType.equals("insert")) {
			conn.setAutoCommit(false);// 通知数据库开启事务(start transaction)
			stmt = conn.createStatement();
			// 设置保存点，下面执行的在检查完成后都将回滚到此处的状态从而不改变数据库中的数据
			sp = conn.setSavepoint();
			String str1=insertToSelect(extraSql);
			System.out.println("______________________________");
			System.out.println(str1);
			System.out.println("______________________________");
			ArrayList<DBRecord> record1 = null;
			ArrayList<DBRecord> record2 = null;
			iCode =stmt.executeUpdate(extraSql);
			System.err.println(iCode + "-----------");
			record1 = samplesDAO.query(conn, str1);
			conn.rollback(sp);
			
			Savepoint sp1 = null;
			sp1 = conn.setSavepoint();
			iCode=stmt.executeUpdate(userSolution);
			System.err.println(iCode + "-----------");
			record2 = samplesDAO.query(conn, str1);
			conn.rollback(sp1);
			if(record2.equals(record1)) {return true;}
			else return false;
		}
		else if(selectType.equals("update")) {
			conn.setAutoCommit(false);// 通知数据库开启事务(start transaction)
			stmt = conn.createStatement();
			// 设置保存点，下面执行的在检查完成后都将回滚到此处的状态从而不改变数据库中的数据
			sp = conn.setSavepoint();
			System.out.println("extraSql:");
			String str1=updateToSelect(extraSql);
			String str2=updateToSelect(userSolution);
			ArrayList<DBRecord> record1 = null;
			ArrayList<DBRecord> record2 = null;
			record1 = samplesDAO.query(conn, str1);
			iCode =stmt.executeUpdate(extraSql);
			System.err.println(iCode + "-----------");
			System.out.println("#2");
			record2 = samplesDAO.query(conn, str1);
			System.out.println("#3");
			conn.rollback(sp);
			
			Savepoint sp1 = null;
			sp1 = conn.setSavepoint();
			ArrayList<DBRecord> record3 = null;
			ArrayList<DBRecord> record4 = null;
			record3 = samplesDAO.query(conn, str2);
			iCode=stmt.executeUpdate(userSolution);
			System.err.println(iCode + "-----------");
			record4 = samplesDAO.query(conn, str2);
			
			Set<String>setProperty=record2.get(0).stringPropertyNames();
			String []stringProperty=new String[setProperty.size()];
			setProperty.toArray(stringProperty);
			for(int j=0;j<record2.size();j++) {
				for(int i=0;i<setProperty.size();i++) {
				System.out.println(record2.get(j).getField(stringProperty[i]));
				if(!record2.get(j).getField(stringProperty[i]).equals(record4.get(j).getField(stringProperty[i]))) {
					System.out.println("##");
				}
				}
			}
			conn.rollback(sp1);
			if(record2.equals(record4)) {return true;}
			else return false;
		}
		else if(selectType.equals("delete")) {
			conn.setAutoCommit(false);// 通知数据库开启事务(start transaction)
			stmt = conn.createStatement();
			// 设置保存点，下面执行的在检查完成后都将回滚到此处的状态从而不改变数据库中的数据
			sp = conn.setSavepoint();
			String str1=deleteToSelect(extraSql);
			String str2=deleteToSelect(userSolution);
			ArrayList<DBRecord> record1 = null;
			ArrayList<DBRecord> record2 = null;
			record1 = samplesDAO.query(conn, str1);
			record2 = samplesDAO.query(conn, str2);
			conn.rollback(sp);
			if(record2.equals(record1)) {return true;}
			else return false;
		}
		return false;
	}

}
